
package com.scalar.chiptrack.users.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.commons.Location;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.vendor.Vendor;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;
import com.scalar.chiptrack.users.VendorAdaptor;
import com.scalar.chiptrack.users.forms.AdapterSettingForm;
import com.scalar.chiptrack.utils.SqlQueryMappings;

//import com.scalar.chiptrack.jobs.Job;
//import com.scalar.chiptrack.jobs.dao.JobDAO;


public class AdaptorSetUpDAO {


    private AdaptorSetUpDAO() {
    }





    public static void  saveVendorAdaptorSetup(DBTransaction dbTransaction,ArrayList vendorLocationList ) throws ScalarException {

        String checkByLocationId = "";
        String notInClause = "";
        String locationId = "";
        Location location = null;

        //int jobId =-1;
        //Job job = null;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
                 for(int i=0;i<vendorLocationList.size();i++){
						locationId = (String)vendorLocationList.get(i);
				       	checkByLocationId = getLocationId(dbTransaction,locationId);
						//System.out.println("checkByLocationID "+checkByLocationId);
						if(checkByLocationId.equals("doesNotExists")){
							location = VendorDAO.getLocationByLocationID(Integer.parseInt(locationId));

							/* --- SKIP JOB INFORMATION --- Balaram on Nov 21, 2002 ---- --- *\

                                                        job = new Job();

							// Default Frequency = 8 is set
							job.setFrequency(8*60);


							String vendorName	= location.getLocationName();
							String locationType = location.getLocationType();

                                                         if (vendorName.equals("TSMC")){
								job.setName("TSMC Adaptor");
								job.setHandler("com.scalar.chiptrack.adaptor.tsmc.TSMCAdaptor");
							}
							else if(vendorName.equals("UMC")){
								job.setName("UMC Adaptor");
								job.setHandler("com.scalar.chiptrack.adaptor.umc.UMCAdaptor");

							}
							else if(vendorName.equals("ASE")){
								 if(locationType.equals("Assembly") || locationType.equals("Die Bank") ){
									job.setName("ASE Assembly Adaptor");
									job.setHandler("com.scalar.chiptrack.adaptor.ase.assembly.ASEAssemblyAdaptor");
								 }

								 else if(locationType.equals("Test") || locationType.equals("Class Store") || locationType.startsWith("Mark")){
									job.setName("ASE Test Adaptor");
									job.setHandler("com.scalar.chiptrack.adaptor.ase.test.ASETestAdaptor");

								 }
							}
							jobId =JobDAO.insertJob(dbTransaction,job);


                                                        \* --- ----  ---- ---- ---- --- ----  --- ---- ---- --- --- ---  */

                                                        insertVendorAdaptor(dbTransaction,locationId);

						}
						if(i!=vendorLocationList.size()-1){
							locationId = locationId+",";
						}
				        notInClause += locationId;
							//System.out.println("notIn "+notInClause);
						}

						//ArrayList jobList = getNonExistingJobId(dbTransaction,notInClause);
						deleteVendorAdaptor(dbTransaction,notInClause);
						//JobDAO.deleteJob(dbTransaction,jobList);


			 }
		    catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save adaptor setup into database", sqle);
        }



     }

	// This method is to check whether the VendorId exists

	public static String getLocationId(DBTransaction dbTransaction,String locationId) throws ScalarException
    {

		         Connection conn = null;
				 PreparedStatement pstmt = null;
				 ResultSet rs    = null;
				 String check    = "doesNotExists";

		try{

				if (dbTransaction == null) {
                conn = DataSource.getConnection();
				}
				else {
                conn = dbTransaction.getConnection();
				}
				pstmt = conn.prepareStatement(SqlQueryMappings.get ("vendor.adaptorSetUp.checkByLocationId"));
				pstmt.setString(1,locationId);
				rs = pstmt.executeQuery();
				if(rs.next()){
				check = "exists";
				}

		   }catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get locationID", sqle);
			}


            finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
			}

			return check;

}

// Ends Here




 // Insert Job in Jobs table
  /*public static int insertJob(DBTransaction dbTransaction) throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int jobId = -1;
		System.out.println("debug 0");


        try{
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
			pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.insertJob"));
			pstmt.setString(1,"");
			pstmt.setString(2,"");
			pstmt.setInt(3,0);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement ("SELECT @@IDENTITY as job_id");
            rset = pstmt.executeQuery();


            if (rset.next() != false) {
                jobId =  rset.getInt ("job_id");
            }

            rset.close();
            rset = null;

        }
        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not insert Job", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                         conn.commit();
                      }
                    }
                   catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

        return jobId;
    }*/
 // Ends here



	  public static void insertVendorAdaptor(DBTransaction dbTransaction,String locationId /*,int jobId*/) throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
         boolean status = false;

        try{
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.insertVendor"));

            pstmt.setString(1, locationId);
            //pstmt.setInt (2, jobId);
            pstmt.executeUpdate();
            status = true;

        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not insert VendorAdaptor", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

	  }

		 public static void deleteVendorAdaptor(DBTransaction dbTransaction,String notInClause) throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
         boolean status = false;

        try{
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
			String sql = "";
			if(!notInClause.equals(""))
			sql="Delete from Vendor_Adaptor where Location_Id not in ( "+notInClause+" )";
			else
			sql="Delete from Vendor_Adaptor";

			System.out.println("Del Sql "+sql);
            pstmt = conn.prepareStatement (sql);
			pstmt.executeUpdate();
			status = true;

        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not Delete Vendor Adaptor ", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }



   }


		public static String[] getAllVendorAdaptor() throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
		ResultSet rs = null;
		String [] vendorID = null;


        try {
             conn  = DataSource.getConnection();
             pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.getAllVendorAdaptor"));
			 rs    = pstmt.executeQuery();
			 if(rs!=null)
			 vendorID = new String[10];

			 int i=0;

			 while(rs.next()){
			 String vendorId = rs.getString(1);
			 vendorID[i]=vendorId;
			 i=i+1;
			 }
        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not Delete Vendor Adaptor ", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

		return 	vendorID;

   }



		public static void saveAdaptorInfo(DBTransaction dbTransaction,String[] vendorID ,String type) throws ScalarException{

				Connection conn = null;
				PreparedStatement pstmt = null;
				boolean status = false;
				String vendorId = "";

				try{
					// Get the connection from regular datasource if its not in a manual transaction
				 if (dbTransaction == null) {
					conn = DataSource.getConnection();
				 }
				else {
					    conn = dbTransaction.getConnection();
				}

				for(int i=0;i<vendorID.length;i++){
				vendorId = vendorID[i];
				pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.insertAdaptorInfo"));
				pstmt.setString(1, vendorId);
				pstmt.setString (2, type);
				pstmt.executeUpdate();
				pstmt.close();
				}
				status = true;

        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not save Adaptor SetUp info", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

	  }


	  public static void deleteAdaptorInfo(DBTransaction dbTransaction) throws ScalarException{

				Connection conn = null;
				PreparedStatement pstmt = null;
				boolean status = false;


				try{
					// Get the connection from regular datasource if its not in a manual transaction
				 if (dbTransaction == null) {
					conn = DataSource.getConnection();
				 }
				else {
					    conn = dbTransaction.getConnection();
				}

				pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.deleteAdaptorInfo"));
				pstmt.executeUpdate();
				status = true;

        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not delete Adaptor Info", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

	  }



	/*public static String[] getAdaptorInfo(String type) throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
		ResultSet rs = null;
		String [] vendorID = null;


        try {
             conn  = DataSource.getConnection();
             pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.getAdaptorInfo"));
			 pstmt.setString(1,type);
			 rs    = pstmt.executeQuery();
			 if(rs!=null)
			 vendorID = new String[10];

			 int i=0;

			 while(rs.next()){
			 String vendorId = rs.getString(1);
			 vendorID[i]=vendorId;
			 i=i+1;
			 }
        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not Delete Vendor Adaptor ", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

		return 	vendorID;

   } */


   public static String[] getAdaptorInfo() throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
		ResultSet rs = null;
		String [] locationID = null;


        try {
             conn  = DataSource.getConnection();
             pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.getAdaptorInfo"));
			 rs    = pstmt.executeQuery();
			 if(rs!=null)
			 locationID = new String[25];

			 int i=0;

			 while(rs.next()){
			 String locationId = rs.getString(1);
			 locationID[i]=locationId;
			 i=i+1;
			 }
        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not get Adaptor Info ", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

		return 	locationID;

   }


   public static ArrayList getVendorAdaptor() throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList vendorLocationList = null;
		Location location = null;


        try {
			 conn  = DataSource.getConnection();
             pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.getVendorAdaptors"));
			 rs    = pstmt.executeQuery();
			 vendorLocationList = new ArrayList();
			 while(rs.next()){
			 location = new Location();
			 location.setLocationId(rs.getInt(1));
			 location.setVendorName(rs.getString(2));
			 vendorLocationList.add(location);
			 }
        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not get Vendor Adaptor ", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

		return 	vendorLocationList;

   }


	public static AdapterSettingForm getVendorAdaptorInfo(int vendorId) throws ScalarException{

				Connection conn = null;
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				AdapterSettingForm adapterSetting = null;

				try{
					// Get the connection from regular datasource if its not in a manual transaction
					adapterSetting = new AdapterSettingForm();
					conn = DataSource.getConnection();
				 	pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.getVendorAdaptorInfo"));

					pstmt.setInt(1,vendorId);
					rs=pstmt.executeQuery();


					while(rs.next()){
					adapterSetting.setUserId(rs.getString("User_ID"));
					adapterSetting.setPassWord(rs.getString("PassWord"));
					adapterSetting.setAcctNo(rs.getString("Acct_No"));
					adapterSetting.setVendorName(rs.getString("L_Vendor_Name"));
					adapterSetting.setOutPutDirectory(rs.getString("OutPutDirectory"));

                                        /*adapterSetting.setJobId(rs.getInt(6));
					adapterSetting.setFrequency(rs.getLong(7)/(60) + "");
					adapterSetting.setJobHandler(rs.getString(8));
					adapterSetting.setJobName(rs.getString(9));*/

					}


        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not get Adaptor Info", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction

            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

		return adapterSetting;

	  }



	  public static void saveVendorAdaptorInfo(DBTransaction dbTransaction,AdapterSettingForm adapterSetting) throws ScalarException{

				Connection conn = null;
				PreparedStatement pstmt = null;
				String vendorId = "";

				try{
					// Get the connection from regular datasource if its not in a manual transaction
				 if (dbTransaction == null) {
					conn = DataSource.getConnection();
				 }
				else {
					    conn = dbTransaction.getConnection();
				}

				pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.updateVendorAdaptorInfo"));
				pstmt.setString(1,adapterSetting.getUserId());
				pstmt.setString(2,adapterSetting.getPassWord());
				pstmt.setString(3,adapterSetting.getAcctNo());
				pstmt.setString(4,adapterSetting.getOutPutDirectory());
				pstmt.setInt(5,adapterSetting.getLocationId());
				pstmt.executeUpdate();



        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not access VendorLocationList", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
						System.out.println("just before commit");
                        conn.commit();
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

	  }






	   public static void saveSameVendorAdaptorInfo(DBTransaction dbTransaction,AdapterSettingForm adapterSetting) throws ScalarException{

				Connection conn = null;
				PreparedStatement pstmt = null;
				String vendorId = "";


				try{
					// Get the connection from regular datasource if its not in a manual transaction
				 if (dbTransaction == null) {
					conn = DataSource.getConnection();
				 }
				else {
					    conn = dbTransaction.getConnection();
				}

				pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.updateSameVendorAdaptorInfo"));
				pstmt.setString(1,adapterSetting.getUserId());
				pstmt.setString(2,adapterSetting.getPassWord());
				pstmt.setString(3,adapterSetting.getAcctNo());
				pstmt.setString(4,adapterSetting.getOutPutDirectory());
				pstmt.setString(5,adapterSetting.getVendorName());
				pstmt.executeUpdate();



        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not save same VendorAdaptor Info", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
						System.out.println("just before commit");
                        conn.commit();
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

	  }


	  /*public static ArrayList getJobId(DBTransaction dbTransaction,String vendorName) throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList jobIdList = null;


        try {

			 if(dbTransaction==null){
			 conn  = DataSource.getConnection();
			 }
			 else{
			 conn  = dbTransaction.getConnection();
			 }

             pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.getJobId"));
			 pstmt.setString(1,vendorName);
			 rs    = pstmt.executeQuery();
			 jobIdList = new ArrayList();
			 while(rs.next()){
			 jobIdList.add(rs.getString(1));
			 }
        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not get Vendor Adaptor ", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

		return 	jobIdList;

   }*/



   public static ArrayList getSelectedVendorName() throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList vendorNameList = null;
		String vendorName = "";



        try {
			 conn  = DataSource.getConnection();
             pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.getVendorSelectedVendorNames"));
			 rs    = pstmt.executeQuery();
			 vendorNameList = new ArrayList();
			 while(rs.next()){
			 vendorName=rs.getString(1);
			 vendorNameList.add(vendorName);
			 }
        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not get Vendor Adaptor ", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

		return 	vendorNameList;

   }


   // Added by Balaram - Nov 20, 2002
   //Modified by Mallikarjun - April 29, 2003
   public static HashMap getSelectedVendorAdaptorNamesAndLocations() throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        HashMap nameLocationMap = new HashMap();

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.getSelectedVendorAdaptorNamesAndLocations"));
            rs = pstmt.executeQuery();
            String l_type = null;
            String v_name = null;

            ArrayList fabList = new ArrayList();
            ArrayList sortList = new ArrayList();
            ArrayList assemblyList = new ArrayList();
            ArrayList testList = new ArrayList();
            ArrayList fgList = new ArrayList();


            while (rs.next()) {

            	l_type = rs.getString ("l_type");
            	v_name = rs.getString ("v_name");

            	if((l_type != null) && (l_type.trim().equalsIgnoreCase(AdaptorConstants.LOCATION_TYPE_FAB))){
            		fabList.add(v_name);
            	}

				if((l_type != null) && (l_type.trim().equalsIgnoreCase(AdaptorConstants.LOCATION_TYPE_SORT))){
            		sortList.add(v_name);
            	}
            	if((l_type != null) && (l_type.trim().equalsIgnoreCase(AdaptorConstants.LOCATION_TYPE_ASSEMBLY))){
            		assemblyList.add(v_name);
            	}

            	if((l_type != null) && (l_type.trim().equalsIgnoreCase(AdaptorConstants.LOCATION_TYPE_TEST))){
            		testList.add(v_name);
            	}

            	if((l_type != null) && (l_type.trim().equalsIgnoreCase(AdaptorConstants.LOCATION_TYPE_FINISHED_GOODS))){
            		fgList.add(v_name);
            	}

            	//System.out.println("l_type = "+l_type+";  v_name = "+v_name);
                //nameLocationMap.put (t_type, v_name);
            }

            nameLocationMap.put (AdaptorConstants.LOCATION_TYPE_FAB, fabList);
            nameLocationMap.put (AdaptorConstants.LOCATION_TYPE_SORT, sortList);
            nameLocationMap.put (AdaptorConstants.LOCATION_TYPE_ASSEMBLY, assemblyList);
            nameLocationMap.put (AdaptorConstants.LOCATION_TYPE_TEST, testList);
            nameLocationMap.put (AdaptorConstants.LOCATION_TYPE_FINISHED_GOODS, fgList);

        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not get Vendor Adaptor ", sql);
        }

        finally{

            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

        return nameLocationMap;
    }




	/*public static ArrayList getNonExistingJobId(DBTransaction dbTransaction,String notInClause) throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList jobIdList = null;


        try {

			 if(dbTransaction==null){
			 conn  = DataSource.getConnection();
			 }
			 else{
			 conn  = dbTransaction.getConnection();
			 }

			 String sql = "";
			 if(!notInClause.equals(""))
             sql  = "Select job_id from vendor_Adaptor where location_Id not in("+notInClause+")";
			 else
			 sql = "Select job_id from vendor_adaptor";

             pstmt = conn.prepareStatement (sql);
			 rs    = pstmt.executeQuery();
			 jobIdList = new ArrayList();
			 while(rs.next()){
			 jobIdList.add(rs.getString(1));
			 }
        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not get Vendor Adaptor ", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

		return 	jobIdList;

   }	*/


	// method added to get vendor adaptor details

   public static ArrayList getVendorAdaptorDetails() throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList vendorAdaptorList = null;
		VendorAdaptor vendorAdaptor = null;



        try {
			 conn  = DataSource.getConnection();
             pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.getVendorAdaptorDetails"));
			 rs    = pstmt.executeQuery();
			 vendorAdaptorList = new ArrayList();
			 while(rs.next()){
			 vendorAdaptor = new VendorAdaptor (rs.getString("l_vendor_name"),
                                                              rs.getString("l_type"),
                                                              rs.getString("user_id"),
                                                              rs.getString("password"),
                                                              rs.getString("outputdirectory"),
                                                              rs.getString("acct_no"));

                            vendorAdaptorList.add(vendorAdaptor);
			 }
        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not get Vendor Adaptor Details ", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

		return 	vendorAdaptorList;

   }



   // Return vendor adaptor details for a given vendor
   // Added by Balaram
    public static VendorAdaptor getVendorAdaptorDetailsByVendorNameAndLocation (String vendorName, String locationType) throws ScalarException{

        if ((vendorName == null) || (locationType == null)) return null;

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        VendorAdaptor vendorAdaptor = null;

        try {
            conn  = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.adaptor.getVendorAdaptorDetailsByVendorNameAndLocation"));
            pstmt.setString (1, vendorName);
            pstmt.setString (2, locationType);

            rs = pstmt.executeQuery();

            if (rs.next()) {
                vendorAdaptor = new VendorAdaptor (vendorName,
                                                     locationType,
                                                     rs.getString("user_id"),
                                                     rs.getString("password"),
                                                     rs.getString("outputdirectory"),
                                                     rs.getString("acct_no"));
            }
            else
            {
                 throw new ScalarException("Could not find Vendor " + vendorName.toUpperCase() +" from the Database");
            }
        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not get Vendor Adaptor Details ", sql);
        }

        finally{

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

	return 	vendorAdaptor;
   }

    public static ArrayList getFGVendorAdaptorInfo (int locationAddressId, String locationType ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList vendorList = new ArrayList();
        Vendor vendor =  new Vendor();

        try
        {
            conn = DataSource.getConnection();
            //pstmt = conn.prepareStatement ( "SELECT Vendor_ID, L_Vendor_Name FROM Location WHERE Address_ID = ? AND L_Type = ? AND L_Is_Deleted = 'N'" );
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "vendor.adaptor.getVendorDetailsByLocationAddressId" ) );
            pstmt.setInt( 1, locationAddressId );
            pstmt.setString( 2, locationType );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                vendor.setVendorId( rs.getInt("Vendor_ID") );
                vendor.setVendorName( rs.getString("L_Vendor_Name") );
                vendorList.add( vendor );
            }
        }
        catch( Exception sql )
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Adaptor Info", sql);
        }

        finally
        {
            if( rs != null )
            {
                try { rs.close(); } catch( SQLException se ){}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException se ){}
            }
            if( conn != null )
            {
                try{ conn.close(); } catch( SQLException se ){}
            }
        }
        return vendorList;
    }




  }